# -*- coding: utf-8 -*-
import urllib,urllib2,cookielib,re,os,sqlite3
from gzip import GzipFile
from StringIO import StringIO
from datetime import datetime,timedelta
from win32com.client import Dispatch
import sys
sys.path.append('..\qp4')
import htmlfile



class ContentEncodingProcessor(urllib2.BaseHandler):
    """A handler to add gzip capabilities to urllib2 requests """

    # add headers to requests
    def http_request(self, req):
        req.add_header("Accept-Encoding", "gzip, deflate")
        return req

    # decode
    def http_response(self, req, resp):
        old_resp = resp
        # gzip
        if resp.headers.get("content-encoding") == "gzip":
            gz = GzipFile(
                fileobj=StringIO(resp.read()),
                mode="r"
            )
            resp = urllib2.addinfourl(gz, old_resp.headers, old_resp.url, old_resp.code)
            resp.msg = old_resp.msg
        # deflate
        if resp.headers.get("content-encoding") == "deflate":
            gz = StringIO( deflate(resp.read()) )
            resp = urllib2.addinfourl(gz, old_resp.headers, old_resp.url, old_resp.code)  # 'class to add info() and
            resp.msg = old_resp.msg
        return resp

# deflate support
import zlib
def deflate(data):   # zlib only provides the zlib compress format, not the deflate format;
    try:               # so on top of all there's this workaround:
        return zlib.decompress(data, -zlib.MAX_WBITS)
    except zlib.error:
        return zlib.decompress(data)








# 去除list中重复的值
def f9(seq):
    # Not order preserving
    return {}.fromkeys(seq).keys()
        
# 判断是否是进口
def hasimport(cd):
    # 第8位为0是出口，1是进口
    return int(cd[8])
    
# 定义获取已经存在的报关单号的list函数
# 由于海关报关系统中的单子修改后日期会变更，查询当天时会把前几天的单子也查询出来，因此更改查询条件为报关单号是否存在
def getexistcdnos(cds):
    has_cdnos=[]
    # 加逗号和单引号分隔符
    cdnos_str="','".join(cds)
    # 前后各加上1个单引号，组成sql中in条件中的字符串
    cdnos_str="'"+cdnos_str+"'"
    print u"查询字符串：",cdnos_str
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_no in ("+cdnos_str+")")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

def gettodaycdnos(search_date):
    has_cdnos=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_date=#"+search_date+"#")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

    
# 获取委托人关联的模板费用，默认为托浦1
def gettmpfee():
    wtr='1'
    tmp={}
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select receive,pay from wtr left join tmp on wtr.tmp=tmp.id where wtr.id="+wtr)
    if not oRS.EOF:
        if oRS.Fields("receive").Value:
            tmp["cd_receive"]=str(oRS.Fields("receive").Value)
        else:
            tmp["cd_receive"]='0'
        if oRS.Fields("pay").Value:
            tmp["cd_pay"]=str(oRS.Fields("pay").Value)
        else:
            tmp["cd_pay"]='0'
    else:
        tmp["cd_receive"]='0'
        tmp["cd_pay"]='0'
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return tmp

def cleanhtml(html):
    pattern='\s+|(&nbsp;)+'
    # pattern='\s+'
    html=re.sub(pattern,' ',html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <','><')



def searchcdpass(cd_no):
    url='http://www.npedi.com/edi/passingInfoAction.do?passno=%s'%cd_no
    print url
    content=urllib2.urlopen(url).read()
    #    f=open(r'file\\'+cd_no+r'.'+str(sign)+r'.html','w')
    #    f.write(content)
    #    f.close()
#    print content
    cd_checks=re.findall('<TD height="24" class="dblue12">.+?</TD>',cleanhtml(content))
    print cd_checks
    if cd_checks:
        passtime=re.sub(r'<.+?>','',cd_checks[6])
        print u'查验：',passtime
        return passtime
#    if cd_checks:
#        oConn = Dispatch('ADODB.Connection')
#        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
#        oConn.Open()
#        oCmd = Dispatch('ADODB.Command')
#        oCmd.ActiveConnection = oConn
#        oCmd.CommandText = 'update cd set cd_check=1 where cd_no in (%s)'%','.join(cd_checks)
#        print oCmd.CommandText
#
#        oCmd.Execute()
#        oCmd = None
#        oConn.Close()
#        oConn = None

def updatecdpass():
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd.id,cd_no from cdpass inner join cd on cdpass.cd=cd.id where cdpass.pass=0")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        passtime=searchcdpass(oRS.Fields("cd_no").Value)
        if passtime:
            oCmd.CommandText = "update cdpass set pass=1,passtime='%s' where cd = %s"%(passtime,oRS.Fields("id").Value)
            print oCmd.CommandText

            oCmd.Execute()
        oRS.MoveNext()
        # print u"查找到的报关单号：",has_cdnos
    oCmd = None
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None



# 新增一个报关单
def addnewcdno(cd):
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    cd_bwtr=str(cd['cd_bwtr'])
    cd_no=cd['cd_no']
    # 进口数据库设置为18，出口为16
    if hasimport(cd_no):
        category='18'
    else:
        category='16'
    cd_company=cd['cd_company']
    cd_voyage=cd['cd_voyage']
    cd_lading=cd['cd_lading']
    cd_verify=cd['cd_verify']
    cd_passno=cd['cd_passno']
    cd_orderno=cd['cd_orderno']
    tmpfee=gettmpfee()
    cd_receive=tmpfee['cd_receive']
    cd_pay=tmpfee['cd_pay']
    cd_boxno=str(int(cd['cd_boxno']))
    # cd_memo=cd['cd_memo']
    oCmd.CommandText = "insert into cd(wtr,bwtr,category,cd_no,cd_date,cd_settledate,cd_company,cd_voyage,cd_lading,cd_verify,cd_post,username,cd_passno,cd_orderno,cd_receive,cd_pay,cd_boxno) values(1,"+cd_bwtr+","+category+",'"+cd_no+"','"+search_date+"','"+search_date+"','"+cd_company+"','"+cd_voyage+"','"+cd_lading+"','"+cd_verify+"',1,1,'"+cd_passno+"','"+cd_orderno+"',"+cd_receive+","+cd_pay+",'"+cd_boxno+"')"
    print oCmd.CommandText
    
    oCmd.Execute()
    
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select @@IDENTITY")
    re_id=oRS.getstring
    oRS.Close()
    oRS=None
    
    # searchcdpass(cd['cd_no'])
    # oCmd.CommandText = "insert into cdpass(cd) values("+re_id+")"
    # oCmd.Execute()
    
    boxs=cd['boxs']
    print boxs
    for box in boxs:
        oCmd.CommandText = "insert into cdbox(cd,box) values("+re_id+",'"+box[0]+"')"
        oCmd.Execute()
    oCmd.CommandText = "insert into record(re_table,re_id,re_operate,re_memo,username) values('cd',"+re_id+",'"+unicode('新增','utf8')+"','"+unicode('同步','utf8')+"',1)"
    oCmd.Execute()
    oCmd = None
    oConn.Close()
    oConn = None
    global UPDATECDS
    UPDATECDS=UPDATECDS+1


    
def updatelog(cdcount,updatecount):
    updatetime=datetime.strftime(datetime.now(),"%H:%M:%S")
    if cdcount:
        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        ul_cont=u"系统同步信息："+updatetime+u" 同步来自宁波海关QP报关单预录入系统 "+str(cdcount)+u" 单，系统新增 "+str(updatecount)+u" 条报关单"
        oCmd.CommandText="insert into ul(ul_date,ul_post,ul_cont) values('"+search_date+"',1,'"+ul_cont+"')"
        oCmd.Execute()
        oCmd = None
        oConn.Close()
        oConn = None
    


def getbwtr():
    bwtrs=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select bwtr.id,bwtr.bwtr,bwtric.* from bwtr,bwtric where bwtr.id=bwtric.bwtr")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("ic_code").Value:
                bwtr={}
                # print oRS.Fields("cd_no").Value
                bwtr["id"]=oRS.Fields("bwtr.id").Value
                bwtr["bwtr"]=oRS.Fields("bwtr.bwtr").Value
                bwtr["ic_code"]=oRS.Fields("ic_code").Value
                bwtr["decno"]=oRS.Fields("decno").Value
                bwtrs.append(bwtr)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return bwtrs

def searchboxcheck(querydatelist):
    boxs=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    sql="select * from boxcheck where check=False and cd_date in (#%s#)"%'#,#'.join(querydatelist)
    print sql
    oRS.Open(sql)                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("box").Value:
            box={}
            box["cd_id"]=oRS.Fields("cd_id").Value
            box["cd_no"]=oRS.Fields("cd_no").Value
            box["cd_date"]=datetime.fromtimestamp(int(oRS.Fields("cd_date").Value)).strftime('%Y-%m-%d')
            box["box_id"]=oRS.Fields("box_id").Value
            box["box"]=oRS.Fields("box").Value
            box["cd_voyage"]=oRS.Fields("cd_voyage").Value
            boxs.append(box)
        oRS.MoveNext()
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    # print boxs

    def filterbox(boxs):
        fboxs=[]
        for box in boxs:
            fbox={}
            fbox['box']=box['box']
            fbox['cd_date']=box['cd_date']
            fbox['cd_voyage']=box['cd_voyage']
            if not fbox in fboxs:
                fboxs.append(fbox)
        return fboxs

    print len(boxs),len(filterbox(boxs))
    for box in filterbox(boxs):
        
        url='http://www.npedi.com/edi/cusmovAction.do?ctnNo=%s&s_VelInfo=&companyCode='%box['box']

#        不使用gzip压缩的话有200k左右
#        content=urllib2.urlopen(url).read()

#        使用gzip压缩接收数据
        encoding_support = ContentEncodingProcessor
        opener = urllib2.build_opener( encoding_support, urllib2.HTTPHandler )
#        直接用opener打开网页，如果服务器支持gzip/defalte则自动解压缩
        content = opener.open(url).read()

        # print cleanhtml(content)
        boxcheck=re.findall('<td height="24" nowrap="nowrap" class="dblue12">.*?</td>',cleanhtml(content))
        print box.values()
        for i in range(len(boxcheck)/13):
            b=[]
            for x in boxcheck[i*13+1:i*13+13]:
                b.append(re.sub('<.+?>','',x).strip())
            print ','.join(b)

    
def searchunrelease(decno):
    url='www.nbedi.com/nbeport_h2000pd/HisSearch/UnReleaseExport.aspx?g=%s&JumpPage=0'%decno
#    url='http://www.nbedi.com/nbeport_h2000pd/HisSearch/ReleaseExport.aspx?DECL_CO=31002155'
    print url
    content=urllib2.urlopen(url).read()
#    f=open(r'file\\'+cd_no+r'.'+str(sign)+r'.html','w')
#    f.write(content)
#    f.close()
    cd_checks=re.findall('\d{18}',content,re.M)
    print u'查验：',cd_checks
    if cd_checks:
        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        oCmd.CommandText = 'update cd set cd_check=1 where cd_no in (%s)'%','.join(cd_checks)
        print oCmd.CommandText

        oCmd.Execute()
        oCmd = None
        oConn.Close()
        oConn = None

def gen_oldfile(conn):
    s=[x[0] for x in conn.execute("select seq_no from dec_mess_status where typist_no='6300000006981' and notice_date>'2012-05-05' ").fetchall()]
    for seq_no in s:
        htmlfile.gen_cdfile(seq_no,conn)
        
def gen_querydate(datecount):
    querydatelist=[]
    queryday=datetime.today()
    for c in xrange(datecount):
        querydatelist.append(datetime.strftime(queryday-timedelta(days=c),'%Y-%m-%d'))
    return querydatelist

# ##########################################
# 主程序开始
# ##########################################
conn=sqlite3.connect('..\qp4\qp.db3')
conn.text_factory = str
conn.row_factory = sqlite3.Row

bwtrs=getbwtr()
print bwtrs
ic_code=[]
for bwtr in bwtrs:
    ic_code.append("'%s'"%bwtr['ic_code'])
typist_no=','.join(ic_code)


print u'开始对被委托人',bwtr['bwtr'],typist_no,u'抓取数据'
#    bwtr['ic_code']='8320000008705'
# 定义查询的日期
search_date=datetime.strftime(datetime.now(),"%Y-%m-%d")
#    search_date='2012-05-25'
querydatecount=4
querydatelist=gen_querydate(querydatecount)
for search_date in querydatelist:

    # 定义更新的报关单数量
    global UPDATECDS
    UPDATECDS=0
    # sql='select seq_no,entry_id from dec_mess_status where status=\'E\' and typist_no=\'%s\' and notice_date like \'%s%%\''%(bwtr['ic_code'],search_date)
    sql='select seq_no,entry_id from dec_mess_status where status=\'E\' and typist_no in(%s) and notice_date like \'%s%%\''%(typist_no,search_date)
    print sql
    search_cdnos=conn.execute(sql).fetchall()
    # print search_cdnos,len(search_cdnos)
    cds=[]
    [cds.append(x[1]) for x in search_cdnos]
    print cds,len(cds)
    new_cdnos=list(set(cds)-set(getexistcdnos(cds)))
    print search_date,u"新产生的报关单号：",len(new_cdnos),new_cdnos
    # print u"手工添加的报关单号：",list(set(gettodaycdnos(search_date))-set(cds))
    new_seqno=[]
    for x in search_cdnos:
        if x[1] in new_cdnos:
            new_seqno.append(x[0])
    print new_seqno
    for seq in new_seqno:
        cd={}
        head=conn.execute('select dec_head.*,dec_freetxt.voyno from dec_head,dec_freetxt where dec_head.seq_no=dec_freetxt.seq_no and dec_head.seq_no=\'%s\''%seq).fetchone()
        # print head
        cd['cd_bwtr']=bwtr['id']
        print u"统一编号：",head['seq_no']
        cd["seq_no"]=head['seq_no']
        print u"报关单号：",head['entry_id']
        cd["cd_no"]=head['entry_id']
        print u"企业名称：",head['trade_name'].decode('utf-8')
        cd["cd_company"]=head['trade_name'].decode('utf-8')
        print u"船名航次：%s/%s"%(head['traf_name'],head['voyno'])
        cd["cd_voyage"]='%s/%s'%(head['traf_name'],head['voyno'])
        print u"核销单号：",head['appr_no']
        if head['appr_no']:
            cd["cd_verify"]=head['appr_no']
        else:
            cd["cd_verify"]=''
        print u"提单号：",head['bill_no']
        cd["cd_lading"]=head['bill_no'].decode('utf-8')
        cd_passno=conn.execute('select cert_code from dec_licensedocu where seq_no=\'%s\' and (docu_code=\'B\' or docu_code=\'A\')'%seq).fetchone()
        print u"通关单号：",cd_passno
        if cd_passno:
            cd['cd_passno']=cd_passno[0]
        else:
            cd['cd_passno']=''
        print u"订单号：",head['contr_no']
        if head['contr_no']:
            cd['cd_orderno']=head['contr_no'].decode('utf-8')
        else:
            cd['cd_orderno']=''
        print u'集装箱数量',head['jzxsl']
        cd['cd_boxno']=head['jzxsl']

        # boxs=[]
        # [boxs.append(re.sub(r'<.+?>','',x)) for x in re.findall(pattern,boxhtml)]
        # cd['boxs']=boxs
        # addnewcdno(cd)
        cd['boxs']=conn.execute('select container_id from dec_container where seq_no=\'%s\''%seq).fetchall()
        print cd['boxs']
        addnewcdno(cd)
        htmlfile.gen_cdfile(cd['seq_no'],conn)
        # searchunrelease(bwtr['decno'])
        print UPDATECDS
    updatelog(len(new_cdnos),UPDATECDS)
searchboxcheck(querydatelist)
# updatecdpass()
# gen_oldfile(conn)
conn.close()